
#!/usr/bin/env python
#===============================================================================
# Standard Library
#===============================================================================
import hashlib
import operator

#===============================================================================
# 3rd party libraries
#===============================================================================
import sqlalchemy
from sqlalchemy import create_engine , ForeignKey
from sqlalchemy import Column, Integer, SmallInteger, String, Text, Boolean, UnicodeText
from sqlalchemy.orm import sessionmaker, relation, backref 
from sqlalchemy.ext.declarative import declarative_base 
from sqlalchemy import UniqueConstraint

#===============================================================================
# Own imports
#===============================================================================
import validate
import config
import enum

#===============================================================================
# GLOBALS
#===============================================================================
Base = declarative_base()
engine = create_engine(config.DB_NAME, echo=config.ECHO, convert_unicode=True, encoding='utf8')
Session = sessionmaker(bind=engine)

#TODO switch off next two lines for scripting (needed for web)
sess = Session()
metadata = Base.metadata
def commit_xxx():
    #TODO tidy up error messages: tuples > text
    try:  
        s.commit() 
        content = 'Saved: '
        valid = True  
    except sqlalchemy.exc.InvalidRequestError, e:
        content = e.args
        valid = False
    except sqlalchemy.exc.IntegrityError, e:
#        content =  e.args # Log this?
        content = '''Cannot save. A field value duplicates the value for this 
                    in another record.\n[Error {0}]'''.format(unicode(str(e.args)))
        valid = False
    except sqlalchemy.exc.OperationalError, e:
        content = e.args[0]
        valid = False
    if not valid:
        #TODO Log this
        s.rollback()
    return valid, content

def commit(s=sess):
    #TODO tidy up error messages: tuples > text
    try:  
        s.commit() 
        content = 'Saved: '
        valid = True  
    except sqlalchemy.exc.InvalidRequestError, e:
        content = e.args
        valid = False
    except sqlalchemy.exc.IntegrityError, e:
#        content =  e.args # Log this?
        content = '''Cannot save. A field value duplicates the value for this 
                    in another record.\n[Error {0}]'''.format(unicode(str(e.args)))
        valid = False
    except sqlalchemy.exc.OperationalError, e:
        content = e.args[0]
        valid = False
    if not valid:
        #TODO Log this
        s.rollback()
    s.close() # need this or you will run out of connections   
    return valid, content
    
def rollback(s=sess):
    '''Function to enable import of rollback in other modules''' 
    s.rollback()

#===============================================================================
# ACCESS
# Class consists of helper functions to simplify accessing data objects with
# simpler method/function calls
#===============================================================================
    
class Access():
    can_delete = False
    '''Determines whether an object can be deleted or merely in-activated'''
        
    def save(self, s=sess):
        '''Saves object to current session. If object has a "name" field, then validate it''' 
        valid = True
        content = self
        if 'name' in self.__dict__.keys():
            valid, content = validate.name(self.name)
        if valid:
            s.add(self)
        return valid, content    
        
    def read(self, s=sess):
        '''Reads object from db and  returns it. Requires that a "name" column 
        is used as unique identifier (if name is a string) or "id" column if 
        this is an integer.''' 
        object = None 
        if self.id:
            filter_string = '{0}.id="{1}"'.format(self.__table__, self.id)
        else:
            filter_string = '{0}.name="{1}"'.format(self.__table__, self.name)
        try:
            object = s.query(self.__class__).filter(filter_string).one()
        except sqlalchemy.orm.exc.NoResultFound, e:
            s.rollback()
            object = None
        except sqlalchemy.exc.IntegrityError, e:
            s.rollback()
            object = None
        return object
        #TODO: catch error or db not connected 'sqlalchemy.exc.OperationalError'
        
    def read_email(self):
        '''Reads object from db and  returns it using email as key.'''  
        filter_string = '{0}.email="{1}"'.format(self.__table__, self.email)
        try:
            object = s.query(self.__class__).filter(filter_string).one()
        except sqlalchemy.orm.exc.NoResultFound, e:
            object = None
        return object 
        
    def all(self, s=sess):
        #TODO name collision, replace with select(None)?
        #TODO docstring
        #TODO: check that a call to Group().all() doesnt fail when no groups exist 
        return s.query(self.__class__).all()
    
#TODO: handle deleting a None object    

    def delete(self, com=False, s=sess):
        '''Deletes the current object'''    
        Obj = self.__class__()
        if Obj.can_delete == True:
            s.delete(self)
        else:
            s.rollback()
            raise Exception ('Cannot delete objects of type "{0}"'.format(self.__class__.__name__))       
    def select(self, selection, s=sess):
        '''Returns a selection of objects. Examples of filters are:
        "_group.id < 4"
        '''
        return s.query(self.__class__).filter(selection).all()
           
    def exists(self, s=sess):
        '''Returns true if selection returns one or more records'''
        filter_string = '{0}.name="{1}"'.format(self.__table__, self.name)
        return bool(s.query(self.__class__).filter(filter_string).count() > 0)

    def create(self, s=sess):
        '''Saves an object to the session unless it already exists'''
        if self.exists():
            return False, "No need to create this record, it already exists"
        else:
            return self.save(s)
        
    def link(self,obj, authority=None, s=sess):
        '''General method for linking one object to another.'''
        valid = False
        content = 'Method "add" not implemented for adding {0} to {1}'.format(
                                                    self.__class__.__name__, obj.__class__.__name__)
        # Adding a user to a groups
        if self.__class__.__name__ == 'User':
            if obj.__class__.__name__ == 'Group':
                if not authority:
                    authority = enum.dict_category(enum.authorities, ['default'])[0][0]
                u2g = User2Group(self.id, obj.id, authority=authority)
                try:
                    valid, content = u2g.save(s)
                except sqlalchemy.exc.IntegrityError:     
                    valid = False
                    content  = 'Error: is this record already linked?'
        return valid, content
         
    def update_link(self,obj, property=None, s=sess):
        '''General method for changing a property on a linking record. e.g the security level
                                                                applicable to a group membership'''
        valid, content = False, None
        left = self.__class__.__name__
        right = obj.__class__.__name__         
        if left == 'User' and right == 'Group':
            filter_string = 'group_id="{0}" and user_id= "{1}"'.format(obj.id, self.id) 
            link = s.query(User2Group).filter(filter_string).one()
            link.authority = property
            try:
                link.save(s)
                valid, content = True, 'Link created'
            except Exception, e:
                #TODO rollback?
                content = e
        else:
             content = '''Method "add" not implemented for adding {0} to {1}'''.format(left,right) 
             valid = False        return valid, content
        
    
    def unlink(self, object, s=sess):
        '''Deletes the link-table record linking the 'left' object to the right object. 's' refers 
        to session db queries are linked to'''
        valid, content = False, None
        left = self.__class__.__name__
        right = object.__class__.__name__
        if left == 'User' and right == 'Group':
            selection ='user_id = {0} and group_id ={1}'.format(self.id,object.id)
            link = s.query(User2Group).filter(selection).one()
            try:
                link.delete(s)
                valid = True
                content = 'link removed between User {0} and Group{1}'.format(self.id, object.id)
            except:
                content = 'Error when removing link between User {0} and Group{1}'.format(self.id, object.id)            
        else:
            raise Exception('Method not implemented for classes {0} and {1}'.format(left, right)) 
        return valid, content

    def type(self):
        #TODO write tests for this function
        '''Returns string giving the type of the object'''
        return self.__type__.__name__
                   
#===============================================================================
#     
# DATA MODEL 
# 
#===============================================================================
  
class User(Base, Access):
    __module__ = __name__
    __tablename__ = 'users'
    id = Column('id', Integer, primary_key=True)
    name = Column('name', String(30), unique = True) 
    email = Column('email', String(255), unique=True, nullable=False)
    password_hash = Column('password_hash',String(32), nullable=False)
    active = Column('active',Boolean, default = True)
 
    def __init__(self, identifier=""):
        if validate.not_number(identifier)[0]:
            self.name = identifier
        else:
            self.id = int(identifier)
        self.can_delete = False
        self.active = True        
    def __repr__(self):
        return '<User %s "%s">' % (self.id, self.name) 
      
    def groups(self, s=sess):
        '''Returns the active groups that a user belongs to.'''
        #TODO: sort groups by descending id 
        return s.query(Group).filter(Group.active==True).join(User2Group).join(User).filter(User.id == self.id).all()       
    def group_links(self, s=sess):
        ''''Returns a list of all groups: tuples where [0] = group name and [1] = True if the user is linked to the group'''           
        links = s.query(Group, User2Group).filter(Group.active==True).outerjoin((User2Group,(User2Group.group_id == Group.id) & (User2Group.user_id == self.id))).all()
        links = [[x[0],x[1] and x[1].authority] for x in links]
        return sorted(links, key=operator.itemgetter(1), reverse=True)     
        
    def hash(self, password):
        '''Returns the a hash for storage of name + salt + password'''
        m = hashlib.md5()
        m.update(config.SALT + password + config.SALT)
        return unicode(m.hexdigest())
    #TODO: require /validate password strength
    
    def authenticates(self, password):
        print 10, password
        print 20, self.hash(password)
        print 30, self.password_hash
        return self.hash(password) == self.password_hash
               
class Group(Base, Access):
    __module__ = __name__
    __tablename__ = 'groups'
    id = Column('id', Integer, primary_key=True)
    name = Column('name', String(30), unique = True, nullable=False)
    active = Column('active',Boolean, default = True)
    
    def __init__(self, identifier=None):
        if validate.not_number(identifier)[0]:
            self.name = identifier
        else:
            self.id = int(identifier)
        self.can_delete = False

    def __repr__(self):
        return '<Group %s "%s">' % (self.id, self.name)
    
    def users(self, s=sess):
        '''Returns active users belonging to the group'''
        return s.query(User).filter(User.active==True).join(User2Group).join(Group).filter(Group.id == self.id).all()
    

class User2Group(Base, Access):
    __module__ = __name__
    __tablename__ = 'users2groups'
    __table_args__ = (UniqueConstraint('user_id', 'group_id'), {} ) 

    id = Column('id', Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('users.id'))
    user = relation(User, order_by='User.id', backref="users")    
    group_id = Column(Integer, ForeignKey('groups.id'))
    group = relation('Group', order_by='Group.id', backref="group")
    authority = Column('authority', SmallInteger)

    
    def __init__(self, user_id=None, group_id=None, authority=None):
        self.user_id = user_id
        self.group_id = group_id
        self.can_delete = True
        self.authority = authority

    def __repr__(self):
        return "<User2Group %s %s>" % (self.user_id, self.group_id)

if __name__ == '__main__':
    import util
    print 'starting...'
    ##-----
    ## Create tables
    if config.TABLES_CREATE:
        metadata.create_all(engine)
    ##-----
    
    u = User(331).read()
    print '10 this is user 331:', u
    print '20 these are the groups user 331 belongs to:', u.groups()
    g = Group(9).read()
    print '30 this is group 9 :', g
    print '40 these are group 9s users:' ,g.users()
    
#    name = util.unique_name('scp')
#    u = User(name)
#    u.password_hash = '12312312312'
#    u.email = '{0}@test.dd'.format(name)
#    print 10, u
#    u.save()
#    print 20, commit()
#    print name

    print 'done!'
    
#!/usr/bin/env python
    
      



#     

